const mysql = require('mysql');
const chalk = require("chalk");
const path = require("path");
const async = require("async");
const mysqlconf = require(path.join(__dirname, './../../conf/db.mysql.conf'));

console.log(chalk.yellow('[hotdog sql] 数据库连接池被创建'));
var pool = mysql.createPool(mysqlconf);

/**
 * 单条sql执行,报错删除修改
 * @param {*} sql 
 * @param {*} callback
 */
function query(sql, callback) {
    return new Promise(async function (resolve, reject) {
        pool.getConnection(async function (err, connection) {
            if (err) throw err;
            await connection.query(sql, function (err, results) {
                console.log(chalk.yellow('[hotdog sql] ' + sql));
                //每次查询都会 回调
                callback(err, results);
                //只是释放链接在缓冲池了，没有被销毁
                connection.release();
                err ? reject(err) : resolve(results);
            });
        });
    });
}

/**
 * 获取connection
 */
const connectHandler = () => new Promise((resolve, reject) => {
    pool.getConnection((err, connection) => {
        if (err) {
            console.error('链接错误：' + err.stack + '\n' + '链接ID：' + connection.threadId)
            reject(err)
        } else {
            resolve(connection)
        }
    })
})

/**
 * 事务提交多条sql语句，sql语句放入数组中
 * @param {多条更新sql语句} sqls 
 */
const excSqlsTranscation = (sqls) => {
    return new Promise(async function (resolve, reject) {
        //得到链接
        const connection = await connectHandler() 
        //开启事务
        await connection.beginTransaction(async function (err) {
            if (err) {
                console.log(chalk.red('开启事务失败'));
                reject(err);
            } else {
                let funcAry = [];
                sqls.forEach(function (sql) {
                    var temp = function (cb) {
                        connection.query(sql,  function (tErr, rows, fields) {
                            if (tErr) {
                                connection.rollback(function () {
                                    console.log(chalk.red("执行sql失败，回滚事务 " + sql));
                                    reject(tErr);
                                });
                            } else {
                                return cb(null, 'ok');
                            }
                        })
                    };
                    funcAry.push(temp);
                });

                async.series(funcAry, function (err, result) {
                    if (err) {
                        connection.rollback(function (err) {
                            console.log(chalk.red("执行事务失败 -1"));
                            console.log(err);
                            connection.release();
                            reject(tErr);
                        });
                    } else {
                        connection.commit(function (err, info) {
                            console.log("transaction info: " + JSON.stringify(info));
                            if (err) {
                                console.log(chalk.red("执行事务失败 -2"));
                                console.log(err);
                                connection.rollback(function (err) {
                                    console.log(chalk.red("执行事务失败 -3"));
                                    console.log(err);
                                    connection.release();
                                    reject(err);
                                });
                            } else {
                                connection.release();
                                resolve("succ");
                            }
                        })
                    }
                });
            }
        })
    });
}

/**拼接insert语句 */
function getInsert(param, colum, tablename) {
    let keys = "";
    let values = "";
    for (let key of colum) {
        if (param[key]||param[key]=='0') {
            keys = keys + key + ",";
            values = values + '\'' + param[key] + '\'' + ',';
        }
    }
    keys = keys.substr(0, keys.length - 1);
    values = values.substr(0, values.length - 1);

    let sql = "insert into " + tablename + "(" + keys + ") values (" + values + ")";
    return sql;
}

/**拼接update语句 */
function getUpdate(param, colum, tablename, whereparm) {
    let keys = "";
    let sql = "";
    for (let key of colum) {
        if (key!='id' && param[key]) {
            keys = keys + key + "=\'" + param[key] + "\',"
        }
    }
    keys = keys.substr(0, keys.length - 1);

    if(whereparm){
        let wheresql = "";
        whereparm.forEach((param)=>{
            wheresql = wheresql + " and " + param.key + " = '"+param.value+"'" ;
        });
        sql = "update " + tablename + " set " + keys + " where 1=1 "+wheresql;
    }else {
        sql = "update " + tablename + " set " + keys + " where id = '" + param["id"] + "'";
    }

    return sql;
}

/**拼接delete语句 */
function getDelete(param, colum, tablename) {
    //需要上层逻辑保证id不为空，否者本层逻辑抛出异常
    let sql = "delete from " + tablename + " where id = '" + param["id"] + "'";
    return sql;
}

/**拼接select语句 */
function getSelect(param, colum, tablename,whereparm) {
    let wheresql = "";
    if(whereparm != "" && typeof (whereparm) != "undefined"){
        whereparm.forEach((param)=>{
            if(param.value != "" && typeof (param.value) != "undefined"){
                wheresql = wheresql + " and " + param.key + " = '"+param.value+"'" ;
            }
        });
    }
    let sql = "select * from " + tablename + " where 1=1 " + wheresql;
    return sql;
}

exports.query = query;
exports.getInsert = getInsert;
exports.getUpdate = getUpdate;
exports.getDelete = getDelete;
exports.getSelect = getSelect;
exports.connectHandler = connectHandler;
exports.excSqlsTranscation = excSqlsTranscation;